![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Tuning the ApplicationWhen analyzing the SQL statement, you should look for two things:
By looking at the SQL statement from these different angles, you may find a problem that you wouldnt find by just looking at it from one viewpoint. For example, consider an application that does not used cached sequences to generate a primary key value. By itself, there is nothing wrong with this approach and the application is probably very efficient. But add a thousand users executing the same application and the problem is quite apparent: You have contention getting the value for the primary key value. The SQL Statement The best way to go about tuning the SQL statements of an existing application is to follow these few steps:
Now take a look at some specifics of these steps. Familiarize Yourself with the Application Look not only at the SQL statements themselves but at the effect of those statements. Make a chart of the different SQL statements and determine the number of accesses each SQL statement makes to each table in the database. This chart can give you an effective visual idea of which tables are being accessed most frequently. Consider the example shown in Figure 26.1.
This chart is a good quick reference for which SQL statements are affecting which tables. You can take this a step further and split the chart into different types of statements such as SELECTs, INSERTs, UPDATEs, DELETEs, and so on. Depending on your system and whether your application is shrink-wrapped or developed in-house, this may be or may not be practical. Use SQL Trace To Analyze the SQL Statements By running SQL Trace on the SQL statements, you can gather much valuable information about the specific operation of each of the SQL statements. SQL Trace provides such valuable information as the following:
You can use this information to determine which SQL statements are efficient and which ones are not. Look for the following indications of inefficient statements:
These clues may point you in the direction of the SQL statements that need to be tuned. You may have to alter these SQL statements to improve their efficiency. By using EXPLAIN PLAN, you may find addition areas that can be improved. For more information about SQL Trace, refer to Chapter 25, Using EXPLAIN PLAN and SQL Trace. Use EXPLAIN PLAN To Analyze Statement Execution By running EXPLAIN PLAN as part of the SQL Trace report, you can get a better idea of how the SQL statement is actually being executed by Oracle. This information (and the information supplied by SQL Trace) helps you judge the efficiency of the SQL statement. Here is a list of some of the things to look for:
You should ask these questions and your own specific questions as you review the EXPLAIN PLAN output. By knowing what your application is supposed to do, you may find important information about the efficiency of your statements by looking at this information. For more about the use of EXPLAIN PLAN, refer to Chapter 25.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |